package com.lxyk.dao.statistic;

import com.lxyk.domain.ClassAttendance;
import com.lxyk.domain.ClassStatisticBO;
import org.apache.ibatis.annotations.*;
import org.springframework.security.core.parameters.P;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;

//班级数据统计
public interface ClassAttendanceDao {
    //根据班级和日期查询
    @Select("select * from class_attendance where cid=#{cid} and DATE_FORMAT(recordDate,'%Y-%m-%d')=DATE_FORMAT(#{recordDate},'%Y-%m-%d')")
    ClassAttendance findByClassAndDate(@Param("cid")Integer cid,@Param("recordDate")Date recordDate);

    //根据年/月查询班级每天的到课率和作业率
    @Select("select * from class_attendance " +
            "where cid=#{cid} and DATE_FORMAT(recordDate,'%Y-%m')=DATE_FORMAT(#{recordDate},'%Y-%m')")
    List<ClassAttendance> findDayDate(@Param("cid")Integer cid,@Param("recordDate") Date recordDate);

    //根据年/月查询班级每个月平均到课率和作业率
    List<ClassStatisticBO> findAvgDate(@Param("cid")Integer cid);

    //修改到课率
    @Update("update class_attendance set attendance=#{avgAttendance} where cid=#{cid} " +
            "and DATE_FORMAT(recordDate,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d')")
    void updateAttendance(@Param("cid")Integer cid, @Param("date")Date date,@Param("avgAttendance") Double avgAttendance);

    //修改作业率
    @Update("update class_attendance set task=#{avgTask} where cid=#{cid} " +
            "and DATE_FORMAT(recordDate,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d')")
    void updateTask(@Param("cid")Integer cid, @Param("date")Date date,@Param("avgTask") Double avgTask);

    //修改日期
//    @Update("update class_attendance set recordDate=#{new Time} " +
//            "where id = (select id from class_attendance where cid=#{cid} and recordDate=#{oldTime})")
//    void updateRecordDate(@Param("cid") Integer cid,@Param("oldTime") Date oldTime,@Param("newTime")Date newTime);

    //添加到课率
    @Insert("insert into class_attendance(recordDate,attendance,cid) values(#{date},#{avgAttendance},#{cid})")
    void addAttendance(@Param("cid")Integer cid, @Param("date")Date date,@Param("avgAttendance") Double avgAttendance);

    //添加作业率
    @Insert("insert into class_attendance(recordDate,task,cid) values(#{date},#{avgTask},#{cid})")
    void addTask(@Param("cid")Integer cid, @Param("date")Date date,@Param("avgTask") Double avgTask);



    //根据班级日期删除数据
    @Delete("delete from class_attendance where cid=#{cid} and DATE_FORMAT(recordDate,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d')")
    void del(@Param("cid")Integer cid, @Param("date")Date date);

//    //查询当前用户关联班级最新的统计数据
//    @Select("select b.className ,c.* " +
//            "from user_class a,classes b,class_attendance c " +
//            "where a.cid=b.id and b.id=c.cid and a.uid=#{userid} and c.recordDate=(select max(recordDate) from class_attendance where cid=b.id)")
//    List<ClassAttendance> findLastData(Integer userid);
    //查询所有班级最新统计数据
    @Select("select b.className ,c.* " +
            "from classes b,class_attendance c " +
            "where  b.id=c.cid  and c.recordDate=(select max(recordDate) from class_attendance where cid=b.id)")
    List<ClassAttendance> findLastData();
}
